/**
 * Writed by yura and mak
 */

package wz.legend.db;

import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.log4j.Logger;
import org.hibernate.Query;
import org.hibernate.classic.Session;

import wz.legend.handlers.auction.Lot;
import wz.legend.handlers.auction.Product;


public class DBManager {
    private static final Logger LOG = Logger.getLogger(DBManager.class);

    private static DBManager instance;

    public static synchronized DBManager getInstance() {
        if (instance == null) {
            instance = new DBManager();
        }
        return instance;
    }

    public synchronized boolean saveLots(List<Lot> lots) {
        Session session = HibernateUtils.getSessionFactory()
                .getCurrentSession();
        try {
            session.beginTransaction();
            for (Lot lot : lots) {
                if (session.get(Lot.class, lot.getId()) != null) {
                    return true;
                }
                session.saveOrUpdate(lot);
            }
            session.getTransaction().commit();

        } catch (Exception e) {
            LOG.error(e, e);
            session.getTransaction().rollback();
        }
        return false;
    }

    public synchronized Product loadProduct(String name, int level, String type) {
        Product product = null;

        Session session = HibernateUtils.getSessionFactory()
                .getCurrentSession();
        try {
            session.beginTransaction();
            List<Product> list = session.createQuery(
                    "from Product where name=:name " + "and level=:level")
                    .setString("name", name).setInteger("level", level).list();
            if (list.size() > 0) {
                product = list.get(0);
            } else {
                product = new Product();
                product.setName(name);
                product.setLevel(level);
                product.setType(type);
                session.save(product);
            }
            session.getTransaction().commit();
        } catch (Exception e) {
            LOG.error(e, e);
            session.getTransaction().rollback();
            product = null;
        }
        return product;
    }

    public List<Object[]> calculatePrices(Date start, Date stop) {
        List<Object[]> list = new ArrayList<Object[]>();
        Session session = HibernateUtils.getSessionFactory()
                .getCurrentSession();
        try {
            session.beginTransaction();
            // select p.name, sum(cost)/sum(amount), min(cost), max(cost) from
            // lot l, product p where l.cost!=0 and p.id=l.PRODUCT_ID group by
            // PRODUCT_ID
            Query query = session.createQuery("select p.name, " +
                    		"min(l.cost/l.amount), " +
                    		"sum(l.cost)/sum(l.amount), " +
                    		"max(l.cost/l.amount), p.id " +
                    		"from Product p , Lot l " +
                    		"where l.cost > 0 and l.product.id = p.id " +
                    		"and l.date >= :start and l.date <= :stop " +
                    		"group by p.id order by p.name");
            query.setDate("start", start);
            query.setDate("stop", stop);
            Iterator iter = query.iterate();
            while (iter.hasNext()) {
                list.add((Object[])iter.next());
            }
            session.getTransaction().commit();
        } catch (Exception e) {
            LOG.error(e, e);
            session.getTransaction().rollback();
        }
        return list;
    }
/*
    public List<Object[]> getLots(long productId) {
        List<Object[]> list = new ArrayList<Object[]>();
        Session session = HibernateUtils.getSessionFactory()
                .getCurrentSession();
        try {
            session.beginTransaction();
            // select p.name, sum(cost)/sum(amount), min(cost), max(cost) from
            // lot l, product p where l.cost!=0 and p.id=l.PRODUCT_ID group by
            // PRODUCT_ID
            Query query = session.createQuery("select p.name, " +
                            "min(l.cost/l.amount), " +
                            "sum(l.cost)/sum(l.amount), " +
                            "max(l.cost/l.amount), p.id " +
                            "from Product p , Lot l " +
                            "where l.cost > 0 and l.product.id = p.id " +
                            "and l.date >= :start and l.date <= :stop " +
                            "group by p.id order by p.name");
            query.setDate("start", start);
            query.setDate("stop", stop);
            Iterator iter = query.iterate();
            while (iter.hasNext()) {
                list.add((Object[])iter.next());
            }
            session.getTransaction().commit();
        } catch (Exception e) {
            LOG.error(e, e);
            session.getTransaction().rollback();
        }
        return list;
    }
    */
}
